-- 为 update_time 字段添加更新触发器
CREATE OR REPLACE FUNCTION update_travel_attraction_time()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER travel_attraction_update_trigger
    BEFORE UPDATE ON travel_attraction
    FOR EACH ROW
EXECUTE FUNCTION update_travel_attraction_time();

-- 为 update_time 字段添加更新触发器
CREATE OR REPLACE FUNCTION update_travel_tag_time()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER travel_tag_update_trigger
    BEFORE UPDATE ON travel_tag
    FOR EACH ROW
EXECUTE FUNCTION update_travel_tag_time();

-- 为 update_time 字段添加更新触发器
CREATE OR REPLACE FUNCTION update_travel_product_time()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER travel_product_update_trigger
    BEFORE UPDATE ON travel_product
    FOR EACH ROW
EXECUTE FUNCTION update_travel_product_time();

-- 添加update_time自动更新触发器
CREATE OR REPLACE FUNCTION update_travel_product_comment_time()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER travel_product_comment_update_trigger
    BEFORE UPDATE ON travel_product_comment
    FOR EACH ROW
EXECUTE FUNCTION update_travel_product_comment_time();

-- 添加update_time自动更新触发器
CREATE OR REPLACE FUNCTION update_travel_order_time()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER travel_order_update_trigger
    BEFORE UPDATE ON travel_order
    FOR EACH ROW
EXECUTE FUNCTION update_travel_order_time();

CREATE OR REPLACE FUNCTION update_travel_product_itinerary_time()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER travel_product_itinerary_update_trigger
    BEFORE UPDATE ON travel_product_itinerary
    FOR EACH ROW
EXECUTE FUNCTION update_travel_product_itinerary_time();

--为行程-景点关联表增加序号自增函数
-- 创建函数：为指定行程ID生成自增的visit_order
CREATE OR REPLACE FUNCTION generate_visit_order(p_itinerary_id BIGINT)
    RETURNS INTEGER AS $$
DECLARE
    v_next_order INTEGER;
    v_sequence_name VARCHAR := 'seq_visit_order_' || p_itinerary_id;
BEGIN
    -- 检查序列是否存在，不存在则创建
    IF NOT EXISTS (SELECT 1 FROM pg_sequences WHERE sequencename = v_sequence_name) THEN
        EXECUTE 'CREATE SEQUENCE ' || quote_ident(v_sequence_name) || ' START WITH 1 INCREMENT BY 1';
    END IF;

    -- 获取下一个序列值
    EXECUTE 'SELECT nextval(' || quote_literal(v_sequence_name) || ')' INTO v_next_order;
    RETURN v_next_order;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION trigger_set_visit_order()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.visit_order := generate_visit_order(NEW.itinerary_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_set_visit_order
    BEFORE INSERT ON travel_itinerary_attraction
    FOR EACH ROW
EXECUTE FUNCTION trigger_set_visit_order();